package br.com.amadeus.android.dao;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {
	
	private static final int DB_VERSION = 15;
	public static final String DB_NAME = "amadeus_droid";
	public static final String TABLE_GOAL = "goal";
	public static final String TABLE_ACTION = "action";
	public static final String TABLE_COURSE = "course";
	public static final String TABLE_RESOURCE = "resource";
	public static final String TABLE_RESOURCETYPE = "resource_type";
	public static final String TABLE_ROLE = "role";
	public static final String TABLE_TASK = "task";
	public static final String TABLE_COURSEUSER = "course_user";
	public static final String TABLE_USER = "user";
	public static final String TABLE_CONFIG = "config";
	public static final String TABLE_HISTORY = "history";
	public static final String TABLE_SOCIAL_HISTORY = "social_history";
	public static final String TABLE_FORUM = "forum";
	public static final String TABLE_MESSAGE = "message";
	
	private final String SCRIPT_TABLE_GOAL = "CREATE TABLE IF NOT EXISTS " + TABLE_GOAL
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "definition VARCHAR(24) NULL ,"
			+ "begin_date VARCHAR(40) NULL ,"
			+ "end_date VARCHAR(40) NULL ,"
			+ "avg_study_time INTEGER NULL ,"
			+ "course_user_id INTEGER NOT NULL ,"
			+ "resolved VARCHAR(8) NULL DEFAULT 'false');";
	
	private final String SCRIPT_TABLE_COURSE = "CREATE TABLE IF NOT EXISTS " + TABLE_COURSE
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT ,"
			+ "name VARCHAR(255) NULL ,"
			+ "begin_date VARCHAR(40) NULL ,"
			+ "end_date VARCHAR(40) NULL ,"
			+ "objectives VARCHAR(2000) NULL ,"
			+ "content VARCHAR(2000) NULL ,"
			+ "active VARCHAR(1) NULL ,"
			+ "amadeus_id INTEGER NULL ,"
			+ "max_amount_students INTEGER NULL ,"
			+ "number_students_course INTEGER NULL );";
		
	private final String SCRIPT_TABLE_USER= "CREATE TABLE IF NOT EXISTS " + TABLE_USER
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "login VARCHAR(255) NULL ,"
			+ "password VARCHAR(255) NULL ,"
			+ "name VARCHAR(255) NULL ,"
			+ "email VARCHAR(40) NULL, "
			+ "role_id INTEGER NULL );";
	
	private final String SCRIPT_TABLE_ROLE= "CREATE TABLE IF NOT EXISTS " + TABLE_ROLE
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "value VARCHAR(45) NULL ," 
			+ "amadeus_id INTEGER NULL );";
	
	private final String SCRIPT_TABLE_ACTION= "CREATE TABLE IF NOT EXISTS " + TABLE_ACTION
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "value VARCHAR(45) NULL );";
	
	private final String SCRIPT_TABLE_COURSEUSER= "CREATE TABLE IF NOT EXISTS " + TABLE_COURSEUSER
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "course_id INTEGER UNSIGNED NOT NULL ,"
			+ "user_id INTEGER UNSIGNED NOT NULL );";
	
	private final String SCRIPT_TABLE_TASK = "CREATE TABLE IF NOT EXISTS " + TABLE_TASK
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "begin_date VARCHAR(40) NULL ,"
			+ "end_date VARCHAR(40) NULL ,"
			+ "resolved VARCHAR(8) NULL DEFAULT 'false' ,"
			+ "action_id INTEGER UNSIGNED NOT NULL ,"
			+ "resource_id INTEGER UNSIGNED NOT NULL ,"
			+ "goal_id INTEGER UNSIGNED NOT NULL );";
	
	private final String SCRIPT_TABLE_RESOURCE = "CREATE TABLE IF NOT EXISTS " + TABLE_RESOURCE
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "name VARCHAR(255) NULL ,"
			+ "value VARCHAR(255) NULL ,"
			+ "extension VARCHAR(45) NULL ,"
			+ "course_id INTEGER UNSIGNED NOT NULL ," 
			+ "forum_id INTEGER UNSIGNED NULL ,"
			+ "amadeus_id INTEGER UNSIGNED NOT NULL );";
	
	private final String SCRIPT_TABLE_CONFIG = "CREATE TABLE IF NOT EXISTS " + TABLE_CONFIG
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "amadeus_url VARCHAR(255) NULL ,"
			+ "user_login VARCHAR(255) NULL ,"
			+ "user_password VARCHAR(255) NULL ,"
			+ "user_name VARCHAR(255) NULL ,"
			+ "version_sync INTEGER NULL );" ;
	
	private final String SCRIPT_TABLE_HISTORY = "CREATE TABLE IF NOT EXISTS " + TABLE_HISTORY
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "course_id INTEGER UNSIGNED NOT NULL ,"
			+ "task_id INTEGER UNSIGNED NOT NULL ,"
			+ "user_id INTEGER UNSIGNED NOT NULL ,"
			+ "creation_date VARCHAR(40) NULL );";
	
	private final String SCRIPT_TABLE_SOCIAL_HISTORY = "CREATE TABLE IF NOT EXISTS " + TABLE_SOCIAL_HISTORY
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "course_name VARCHAR(255) NULL ,"
			+ "user_name VARCHAR(255) NULL ,"
			+ "user_login VARCHAR(255) NULL ,"
			+ "action_value VARCHAR(255) NULL ,"
			+ "resource_name VARCHAR(255) NULL ,"
			+ "creation_date VARCHAR(40) NULL ,"
	        + "amadeus_id INTEGER UNSIGNED NOT NULL );";
	
	private final String SCRIPT_TABLE_FORUM = "CREATE TABLE IF NOT EXISTS " + TABLE_FORUM
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "name VARCHAR(255) NULL ,"
			+ "description VARCHAR(255) NULL ,"
			+ "creation_date VARCHAR(40) NULL ,"
			+ "amadeus_id INTEGER UNSIGNED NOT NULL );";
	
	private final String SCRIPT_TABLE_MESSAGE = "CREATE TABLE IF NOT EXISTS " + TABLE_MESSAGE
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ "body VARCHAR(255) NULL ,"
			+ "date VARCHAR(40) NULL ,"
			+ "person_name VARCHAR(255) NULL ,"
			+ "person_login VARCHAR(255) NULL ,"
			+ "forum_id INTEGER UNSIGNED NOT NULL ,"
			+ "amadeus_id INTEGER UNSIGNED NOT NULL );";
	
	private final String[] SCRIPT_INSERT_DEFAULT_ACTIONS = {"INSERT INTO action(_id,value) VALUES (1,\"Ler\"); ",
			"INSERT INTO action(_id,value) VALUES (2,\"Escrever\"); " ,
			"INSERT INTO action(_id,value) VALUES (3,\"Responder\"); " ,
			"INSERT INTO action(_id,value) VALUES (4,\"Escutar\"); " ,
			"INSERT INTO action(_id,value) VALUES (5,\"Assistir\"); " ,
			"INSERT INTO action(_id,value) VALUES (6,\"Revisar\"); " ,
			"INSERT INTO action(_id,value) VALUES (7,\"Fazer\"); " };

	public DBHelper(Context ctx) {
		super(ctx, DB_NAME, null, DB_VERSION);
	}

	public void onCreate(SQLiteDatabase db) {
		db.execSQL(SCRIPT_TABLE_COURSE);
		db.execSQL(SCRIPT_TABLE_GOAL);
		db.execSQL(SCRIPT_TABLE_ACTION);
		db.execSQL(SCRIPT_TABLE_COURSEUSER);
		db.execSQL(SCRIPT_TABLE_RESOURCE);
		db.execSQL(SCRIPT_TABLE_ROLE);
		db.execSQL(SCRIPT_TABLE_TASK);
		db.execSQL(SCRIPT_TABLE_USER);
		db.execSQL(SCRIPT_TABLE_CONFIG);
		db.execSQL(SCRIPT_TABLE_HISTORY);
		db.execSQL(SCRIPT_TABLE_SOCIAL_HISTORY);
		db.execSQL(SCRIPT_TABLE_FORUM);
		db.execSQL(SCRIPT_TABLE_MESSAGE);
		for (int i = 0; i < this.SCRIPT_INSERT_DEFAULT_ACTIONS.length; i++) {
			db.execSQL(SCRIPT_INSERT_DEFAULT_ACTIONS[i]);
		}
	}

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Nada pra fazer na primeira versão ainda
		db.execSQL(SCRIPT_TABLE_COURSE);
		db.execSQL(SCRIPT_TABLE_GOAL);
		db.execSQL(SCRIPT_TABLE_ACTION);
		db.execSQL(SCRIPT_TABLE_COURSEUSER);
		db.execSQL(SCRIPT_TABLE_RESOURCE);
		db.execSQL(SCRIPT_TABLE_ROLE);
		db.execSQL(SCRIPT_TABLE_TASK);
		db.execSQL(SCRIPT_TABLE_USER);
		db.execSQL(SCRIPT_TABLE_CONFIG);
		db.execSQL(SCRIPT_TABLE_HISTORY);
		db.execSQL(SCRIPT_TABLE_SOCIAL_HISTORY);
		db.execSQL(SCRIPT_TABLE_FORUM);
		db.execSQL(SCRIPT_TABLE_MESSAGE);
	}
}
